package com.chen.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class DbUtil {
    static Logger logger = LoggerFactory.getLogger("DbUtil");

    private static int initCount = 5;

    private static int maxCount = 10;

    private static int currentCount = 0;

    private static int MAX_NUMBER = 30;

    private static List<Connection> connectionPool = new LinkedList<>();

    private static Lock dbLock = new ReentrantLock();

    private static Lock poolLock = new ReentrantLock();

    static {
        try {
            Class.forName("org.sqlite.JDBC");
            for (int i = 0; i < initCount; i++) {
                currentCount++;
                returnConnection(createConnection());
            }
        } catch (ClassNotFoundException e) {
            logger.warn("数据库连接池初始化失败");
        }
        boolean table = createTable();
    }

    static private boolean createTable() {
        Connection connection = null;
        Statement statement = null;
        boolean execute = false;
        try {
            connection = getConnection();
            statement = connection.createStatement();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        String sql = "CREATE TABLE brand" +
                "(ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                " URL CHAR(50)           NOT NULL, " +
                " INFO  TEXT             NOT NULL,  " +
                " TYPE CHAR(50) NOT NULL        )";
        String sql1 = "CREATE TABLE typeInfo" +
                "(ID INTEGER PRIMARY KEY AUTOINCREMENT," +
                " NAME CHAR(50)    UNIQUE      NOT NULL, " +
                " STATE INTEGER     DEFAULT 0  NOT NULL, " +
                " TYPE CHAR(10)  NOT NULL       )";
        try {
            execute = statement.execute(sql);
        } catch (SQLException throwables) {
            logger.info("brand表已经创建");
        }
        try {
            statement.execute(sql1);
        } catch (SQLException throwables) {
            logger.info("typeInfo表已经创建");
        }
        try {
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            returnConnection(connection);
        }
        return execute;
    }

    static public void brandAdd(String url, String info) throws SQLException {
        long a = System.currentTimeMillis();
        logger.warn("插入url" + url);
        Connection connection = getConnection();
        try {
            String sql = "INSERT INTO brand(URL,INFO,TYPE)VALUES(?,?,?);";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, url);
            preparedStatement.setString(2, info);
            preparedStatement.setString(3, JSON.parseObject(info).getJSONObject("extras").getString("type"));
            dbLock.lock();
            try {
                int execute = preparedStatement.executeUpdate();
            } catch (SQLException e) {
                logger.warn("插入品牌出错");
            } finally {
                dbLock.unlock();
            }
            preparedStatement.close();
        } finally {
            returnConnection(connection);
        }
        logger.debug("完成插入用时：" + (System.currentTimeMillis() - a));
    }

    static public Map.Entry<String, String> brandPoll() throws SQLException {
        long a = System.currentTimeMillis();
        Map.Entry entry = null;
        Connection connection = getConnection();
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = null;
            String url = null;
            String info = null;
            dbLock.lock();
            try {
                resultSet = statement.executeQuery("SELECT * FROM brand LIMIT 1 ");
                if (resultSet.isClosed()) {
                    return null;
                }
                url = resultSet.getString("URL");
                info = resultSet.getString("INFO");
                int id = resultSet.getInt("ID");
                resultSet.close();
                statement.executeUpdate("DELETE FROM brand WHERE ID=" + id);
            } catch (SQLException e) {
                logger.warn("删除无效品牌出错");
            } finally {
                dbLock.unlock();
            }
            statement.close();
            entry = new AbstractMap.SimpleEntry(url, info);
        } finally {
            returnConnection(connection);
        }
        logger.debug("完成取出用时：" + (System.currentTimeMillis() - a));
        return entry;
    }

    static public boolean isDuplicate(String str) throws SQLException {
        Connection connection = getConnection();
        try {
            String sql = "SELECT * FROM brand  WHERE URL=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, str);
            ResultSet i = null;
            dbLock.lock();
            try {
                i = preparedStatement.executeQuery();
            } catch (SQLException e) {
                logger.warn("查询是否重复出现错误");
            } finally {
                dbLock.unlock();
            }
            int row = i.getRow();
            i.close();
            preparedStatement.close();
            return row > 0;
        } finally {
            returnConnection(connection);
        }
    }

    private static Connection getConnection() throws SQLException {
        Connection conn = null;
        if (connectionPool.size() > 0) {
            poolLock.lock();
            try {
                conn = connectionPool.remove(0);
            } finally {
                poolLock.unlock();
            }
        } else {
            if (currentCount < maxCount) {
                conn = createConnection();
                currentCount++;
            } else if (maxCount < MAX_NUMBER) {
                maxCount += 10;
                conn = createConnection();
                currentCount++;
                throw new SQLException("已经没有数据库连接,扩容中..." + currentCount + ":" + connectionPool.size());
            }
        }
        return conn;
    }

    private static void returnConnection(Connection connection) {
        poolLock.lock();
        try {
            connectionPool.add(connection);
        } finally {
            logger.debug("归还成功:" + connectionPool.size());
            poolLock.unlock();
        }
    }

    private static Connection createConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:sqlite:Queue.db");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        logger.debug("新增连接");
        return conn;
    }

    static public void typeInfoAdd(Set<Map.Entry<String, String>> list) throws SQLException {
        long a = System.currentTimeMillis();
        Connection connection = getConnection();
        try {
            String sql = "INSERT INTO typeInfo(NAME,TYPE)VALUES (?,?);";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (Map.Entry<String, String> stringStringEntry : list) {
                JSONObject jsonObject = JSON.parseObject(stringStringEntry.getValue());
                preparedStatement.setString(1, stringStringEntry.getKey());
                preparedStatement.setString(2, jsonObject.getString("type"));
                dbLock.lock();
                try {
                    preparedStatement.executeUpdate();
                } catch (org.sqlite.SQLiteException e) {
                    logger.warn("种类已经存在");
                } finally {
                    dbLock.unlock();
                }
            }
            preparedStatement.close();
        } finally {
            returnConnection(connection);
        }
        logger.debug("完成类型插入" + (System.currentTimeMillis() - a));
    }

    static public List<String> getExcept() throws SQLException {
        ArrayList<String> res = new ArrayList<>();
        Connection connection = getConnection();
        try {
            Statement statement = connection.createStatement();
            String sql = "SELECT NAME FROM typeInfo\n" +
                    "WHERE STATE = 0\n" +
                    "EXCEPT\n" +
                    "SELECT TYPE FROM brand;";
            dbLock.lock();
            ResultSet resultSet = null;
            try {
                resultSet = statement.executeQuery(sql);
            } catch (SQLException e) {
                logger.warn("查询差集出错");
            } finally {
                dbLock.unlock();
            }
            while (resultSet.next()) {
                String temp = resultSet.getString(1);
                res.add(temp);
            }
            statement.close();
        } finally {
            returnConnection(connection);
        }
        updateTypeInfo(res);
        return res;
    }

    private static void updateTypeInfo(List<String> input) throws SQLException {
        Connection connection = getConnection();
        try {
            String sql = "UPDATE typeInfo SET STATE = 1 WHERE NAME = ?;";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (String s : input) {
                preparedStatement.setString(1, s);
                dbLock.lock();
                try {
                    preparedStatement.executeUpdate();
                } catch (SQLException e) {
                    logger.warn("更新状态出错");
                } finally {
                    dbLock.unlock();
                }
            }
            preparedStatement.close();
        } finally {
            returnConnection(connection);
        }
    }


}
